Computer systems for the storage and retrieval of data typically include a mechanism for the transfer of data from one system or database to another. Where data is stored in accordance with a relational model, the data will typically be exchanged between one database management system (DBMS) and another or between databases in the same DBMS, by way of one number of predefined flat file formats which correspond to tabular representations of the data. Examples of such file formats are ASC or DEL formats (non-delimited and delimited ASCII representations used in the DB2(trademark) system), the PC IXF file format (a binary representation used in the DB2(trademark) system) and the WSF file format (a format corresponding to a Lotus(trademark) spreadsheet representation of data). In object-relational databases, a table in the database is modelled as a class of objects. A user of an object-relational database may define table hierarchies by extending a supertype which defines a table in the database with additional attributes to define subtypes which are in turn used for table definition in the database. The user-defined hierarchy of types defines a table hierarchy which may be populated with data in accordance with the needs of a user. This table hierarchy may be represented as a tree-structure in which the nodes are type definitions corresponding to tables in the database. Once a user has created a database having a user-defined table hierarchy in an object-relational DBMS a file format reflecting the table hierarchy must be used to move the data a new system or database. Data in a user-defined object-relational database table hierarchy cannot be directly exported (i.e. copied without a mapping or transformation step) from one DBMS and imported into another DBMS in the flat file formats used for non-object oriented relational databases.
Prior art systems contain features to permit the translation of hierarchical data into relational models. For example, U.S. Pat. No. 5,201,046 to Goldberg dated Apr. 6, 1993 discloses a DBMS which stores, retrieves and manipulates directed graph data structures in a relational database. Similarly, U.S. Pat. No. 5,799,310 to Anderson dated Aug. 25, 1998 describes relational extenders for handling complex data types. In U.S. Pat. No. 5,295,256 to Bapat dated Mar. 15, 1994, a translator for translating instances of objects in an object oriented programming environment into a relational database schema is described.
Such prior art systems, do not consider the translation of data from an object-relational database hierarchy to a standard flat file data exchange file format for transfer to a second object-relational database. Rather such systems extend relational models by defining new types within relational database columns, as in Anderson and Goldberg, or use multiple tables to persistently store instances of run-time objects used in an object-oriented programming language, as in Bapat. These prior art approaches map the hierarchical data into SQL schemas. It is therefore desirable to have a computer system which includes the capacity to export object-relational hierarchical data in a pre-defined flat file format to permit exchange to a second object-relational database.
According to one aspect of the present invention, there is provided an improved system for manipulating data in object-relational databases.
According to another aspect of the present invention, there is provided a computer system for manipulating data in an object relational source database, the source database having data tables and a defined table type hierarchy with an associated tree structure, the system comprising, means for transforming data from the source database into a flat file data transfer file, comprising rows and columns, the means for transforming the source database comprising means for traversing the tree structure of the table type hierarchy using a tree traversal order, means for defining the columns in the data transfer file to correspond to the type definitions in the table type hierarchy for the source database, the means for defining the columns further comprising means for defining subcolumns corresponding to attributes defined in a respective type definition for a given column, the means for defining the columns further comprising means for defining a type identifier column corresponding to a type identifier determined during the traversal of the tree structure, the type identifier column values corresponding to defined types in the table type hierarchy and determined by the tree traversal order, the means for transforming data from the source database into the data transfer file further comprising means for copying rows of data from the tables in the source database into the data transfer file, each row in the data transfer file having a type identifier column value corresponding to the table in the table type hierarchy of the source database which is the source of the data in the row in the data transfer file.
According to another aspect of the present invention, there is provided the above computer system with input means for transforming data in the data transfer file to a target object relational database, the target object relational database having a table type hierarchy corresponding to the table type hierarchy of the source database, the input means comprising means for copying each row of data in the data transfer file to a target table in the target database, the target table being identified by the type identifier column value for the row of data in the data transfer file, the data for each column in the row of data in the data transfer file being copied to the column in the target table as defined by the tree traversal order.
According to another aspect of the present invention, there is provided the above computer system in which the tree traversal order is user-definable and is constrained to be a pre-order, depth-first traversal and in which the default tree traversal order is a pre-order depth-first traversal with a sibling selection based on creation time for the type.
According to another aspect of the present invention, there is provided the above computer system in which characteristics defining the table type hierarchy for the source database are stored in the data transfer file and in which the characteristics defining the table type hierarchy for the source database comprise a traverse order table and a type identifier control table.
According to another aspect of the present invention, there is provided the above computer system in which hierarchy characteristics defining the table type hierarchy for the source database are stored in the data transfer file and which further comprises means for creating the table type hierarchy for the target database from the hierarchy characteristics.
According to another aspect of the present invention, there is provided the above computer system in which the input means further comprises means for accepting an attribute list from a user, the attribute list corresponding to specified columns in the data transfer file and means for inputting only the specified columns of data from the data transfer file to the target database.
According to another aspect of the present invention, there is provided a computer program product for use with a computer comprising a central processing unit and random access memory, said computer program product comprising a computer usable medium having computer readable code means embodied in said medium for implementing a system as described above.
According to another aspect of the present invention, there is provided a method for manipulating data in an object relational source database, the source database having data tables and a defined table type hierarchy with an associated tree structure, the method comprising the steps of transforming data from the source database into a flat file data transfer file, comprising rows and columns, step of transforming the source database comprising the further steps of traversing the tree structure of the table type hierarchy using a tree traversal order, defining the columns in the data transfer file to correspond to the type definitions in the table type hierarchy for the source database, defining subcolumns corresponding to attributes defined in a respective type definition for a given column, defining a type identifier column corresponding to a type identifier determined during the traversal of the tree structure, the type identifier column values corresponding to defined types in the table type hierarchy and determined by the tree traversal order, copying rows of data from the tables in the source database into the data transfer file, each row in the data transfer file having a type identifier column value corresponding to the table in the table type hierarchy of the source database which is the source of the data in the row in the data transfer file.
According to another aspect of the present invention, there is provided the above method further comprising input steps for transforming data in the data transfer file to a target object relational database, the target object relational database having a table type hierarchy corresponding to the table type hierarchy of the source database, the input steps comprising copying each row of data in the data transfer file to a target table in the target database, the target table being identified by the type identifier column value for the row of data in the data transfer file, the data for each column in the row of data in the data transfer file being copied to the column in the target table as defined by the tree traversal order.
According to another aspect of the present invention, there is provided a computer program product tangibly embodying a program of instructions executable by a computer to perform the method steps described above.
Advantages of the present invention include a system which provides the ability to transfer object relational data from one database to another using a defined flat file format data transfer file.